<template>
  <div class="database-page">
    <el-card class="info-card">
      <template #header>
        <div class="card-header">
          <h3>数据库信息</h3>
          <el-button type="primary" size="small" @click="refreshDbInfo">刷新</el-button>
        </div>
      </template>
      <el-descriptions :column="2" border>
        <el-descriptions-item label="数据库类型">SQLite</el-descriptions-item>
        <el-descriptions-item label="版本">{{ dbInfo.version || '-' }}</el-descriptions-item>
        <el-descriptions-item label="文件路径">{{ dbInfo.path || '-' }}</el-descriptions-item>
        <el-descriptions-item label="文件大小">{{ dbInfo.size || '-' }}</el-descriptions-item>
        <el-descriptions-item label="表数量">{{ dbInfo.tableCount || '-' }}</el-descriptions-item>
        <el-descriptions-item label="总记录数">{{ dbInfo.recordCount || '-' }}</el-descriptions-item>
      </el-descriptions>
    </el-card>

    <el-card class="tables-card">
      <template #header>
        <div class="card-header">
          <h3>数据库表</h3>
          <el-button type="primary" size="small" @click="loadTables">刷新</el-button>
        </div>
      </template>
      <el-table :data="tables" style="width: 100%" height="250px" v-loading="loadingTables">
        <el-table-column prop="name" label="表名" width="180" />
        <el-table-column prop="count" label="记录数" width="120" />
        <el-table-column prop="type" label="类型" width="120" />
        <el-table-column prop="sql" label="创建SQL" show-overflow-tooltip />
        <el-table-column fixed="right" label="操作" width="150">
          <template #default="scope">
            <el-button link type="primary" @click="viewTableStructure(scope.row)">结构</el-button>
            <el-button link type="primary" @click="viewTableData(scope.row)">数据</el-button>
          </template>
        </el-table-column>
      </el-table>
    </el-card>

    <el-card class="sql-card">
      <template #header>
        <div class="card-header">
          <h3>SQL查询</h3>
          <div>
            <el-button type="success" size="small" @click="executeSql" :loading="executing">执行</el-button>
            <el-button size="small" @click="sqlContent = ''">清空</el-button>
          </div>
        </div>
      </template>
      <el-input
        v-model="sqlContent"
        type="textarea"
        :rows="5"
        placeholder="输入SQL语句..."
        class="sql-input"
      />
      <div class="result-header">
        <h4>执行结果</h4>
        <span v-if="sqlResult.time">耗时: {{ sqlResult.time }}ms</span>
      </div>
      <div class="sql-result" v-loading="executing">
        <template v-if="sqlResult.data && sqlResult.data.length > 0">
          <el-table :data="sqlResult.data" style="width: 100%" max-height="250px" border>
            <el-table-column
              v-for="(col, index) in sqlResult.columns"
              :key="index"
              :prop="col"
              :label="col"
              show-overflow-tooltip
            />
          </el-table>
          <div class="result-summary">共 {{ sqlResult.data.length }} 条记录</div>
        </template>
        <template v-else-if="sqlResult.isExecuted">
          <el-alert
            v-if="sqlResult.error"
            title="执行错误"
            type="error"
            :description="sqlResult.error"
            show-icon
          />
          <el-result
            v-else-if="sqlResult.changes !== undefined"
            icon="success"
            title="执行成功"
            :sub-title="`影响 ${sqlResult.changes} 行数据${sqlResult.lastInsertRowid ? '，最后插入ID: ' + sqlResult.lastInsertRowid : ''}`"
          />
          <el-empty v-else description="没有返回数据" />
        </template>
        <template v-else>
          <el-empty description="请输入并执行SQL语句" />
        </template>
      </div>
    </el-card>

    <!-- 表结构对话框 -->
    <el-dialog
      v-model="structureDialog.visible"
      :title="`表结构: ${structureDialog.tableName}`"
      width="70%"
    >
      <el-table :data="structureDialog.columns" style="width: 100%" border>
        <el-table-column prop="cid" label="序号" width="70" />
        <el-table-column prop="name" label="列名" width="150" />
        <el-table-column prop="type" label="数据类型" width="120" />
        <el-table-column prop="notnull" label="非空">
          <template #default="scope">
            {{ scope.row.notnull ? '是' : '否' }}
          </template>
        </el-table-column>
        <el-table-column prop="dflt_value" label="默认值" />
        <el-table-column prop="pk" label="主键">
          <template #default="scope">
            {{ scope.row.pk ? '是' : '否' }}
          </template>
        </el-table-column>
      </el-table>
    </el-dialog>

    <!-- 表数据对话框 -->
    <el-dialog
      v-model="dataDialog.visible"
      :title="`表数据: ${dataDialog.tableName}`"
      width="90%"
      top="5vh"
    >
      <div class="data-dialog-toolbar">
        <el-input
          v-model="dataDialog.search"
          placeholder="搜索..."
          prefix-icon="Search"
          clearable
          @input="onDataSearchChange"
          style="width: 250px"
        />
        <div>
          <el-select v-model="dataDialog.pageSize" @change="loadTableData">
            <el-option :value="10" label="10条/页" />
            <el-option :value="20" label="20条/页" />
            <el-option :value="50" label="50条/页" />
            <el-option :value="100" label="100条/页" />
          </el-select>
        </div>
      </div>
      <el-table :data="dataDialog.data" style="width: 100%" max-height="500px" border v-loading="dataDialog.loading">
        <el-table-column
          v-for="col in dataDialog.columns"
          :key="col"
          :prop="col"
          :label="col"
          show-overflow-tooltip
        />
      </el-table>
      <div class="dialog-pagination">
        <el-pagination
          v-model:current-page="dataDialog.page"
          v-model:page-size="dataDialog.pageSize"
          :page-sizes="[10, 20, 50, 100]"
          :background="true"
          layout="total, sizes, prev, pager, next, jumper"
          :total="dataDialog.total"
          @size-change="loadTableData"
          @current-change="loadTableData"
        />
      </div>
    </el-dialog>
  </div>
</template>

<script setup lang="ts">
import { ref, reactive, onMounted } from 'vue';
import { ElMessage, ElMessageBox } from 'element-plus';

// 数据库信息
const dbInfo = reactive({
  version: '',
  path: '',
  size: '',
  tableCount: 0,
  recordCount: 0
});

// 表列表
const tables = ref<TableData[]>([]);
const loadingTables = ref(false);

// SQL查询相关
const sqlContent = ref('');
const executing = ref(false);
const sqlResult = reactive({
  data: [] as any[],
  columns: [] as string[],
  error: '',
  isExecuted: false,
  changes: undefined as number | undefined,
  lastInsertRowid: undefined as number | undefined,
  time: 0
});

// 表结构对话框
const structureDialog = reactive({
  visible: false,
  tableName: '',
  columns: [] as any[]
});

// 表数据对话框
const dataDialog = reactive({
  visible: false,
  tableName: '',
  columns: [] as string[],
  data: [] as any[],
  loading: false,
  page: 1,
  pageSize: 20,
  total: 0,
  search: '',
  searchTimeout: null as any
});

// 加载数据库信息
async function refreshDbInfo() {
  try {
    // 获取数据库版本
    const versionResult = await window.elec.db.query('SELECT sqlite_version() as version');
    if (versionResult && versionResult.length > 0) {
      dbInfo.version = versionResult[0].version;
    }

    // 获取数据库文件信息和表数量
    const infoResult = await window.elec.db.query(
      `SELECT count(*) as tableCount FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'`
    );

    if (infoResult && infoResult.length > 0) {
      dbInfo.tableCount = infoResult[0].tableCount;
    }

    // 获取数据库路径
    const dbPathInfo = await window.elec.db.query('PRAGMA database_list');
    if (dbPathInfo && dbPathInfo.length > 0) {
      dbInfo.path = dbPathInfo[0].file || '-';
    }

    // 文件大小无法通过SQLite直接获取
    dbInfo.size = '由SQLite管理';

    // 获取总记录数(估计值)
    let totalRecords = 0;
    for (const table of tables.value) {
      const countResult = await window.elec.db.query(`SELECT count(*) as count FROM "${table.name}"`);
      if (countResult && countResult.length > 0) {
        totalRecords += countResult[0].count;
      }
    }
    dbInfo.recordCount = totalRecords;

  } catch (error: any) {
    ElMessage.error(`获取数据库信息失败: ${error.message}`);
  }
}

// 加载数据库表
async function loadTables() {
  loadingTables.value = true;
  try {
    const result = await window.elec.db.query(
      `SELECT name, type, sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name`
    );

    // 获取每个表的记录数
    const tablesWithCount = await Promise.all(
      result.map(async (table: any) => {
        const countResult = await window.elec.db.query(`SELECT count(*) as count FROM "${table.name}"`);
        return {
          ...table,
          count: countResult[0].count
        };
      })
    );

    tables.value = tablesWithCount;
  } catch (error: any) {
    ElMessage.error(`加载表列表失败: ${error.message}`);
  } finally {
    loadingTables.value = false;
  }
}

// 执行SQL查询
async function executeSql() {
  if (!sqlContent.value.trim()) {
    ElMessage.warning('请输入SQL语句');
    return;
  }

  executing.value = true;
  sqlResult.isExecuted = true;
  sqlResult.error = '';

  try {
    const startTime = performance.now();

    // 判断是查询还是执行
    const sqlLower = sqlContent.value.trim().toLowerCase();
    const isQuery =
      sqlLower.startsWith('select') ||
      sqlLower.startsWith('pragma') ||
      sqlLower.startsWith('explain');

    if (isQuery) {
      const result = await window.elec.db.query(sqlContent.value);
      sqlResult.data = result || [];
      sqlResult.columns = result && result.length > 0
        ? Object.keys(result[0])
        : [];
      sqlResult.changes = undefined;
      sqlResult.lastInsertRowid = undefined;
    } else {
      // 对于非查询操作，先确认
      await ElMessageBox.confirm(
        '你正在执行一个修改数据库的操作，确定继续吗？',
        '警告',
        {
          confirmButtonText: '执行',
          cancelButtonText: '取消',
          type: 'warning'
        }
      );

      const result = await window.elec.db.execute(sqlContent.value);
      sqlResult.data = [];
      sqlResult.columns = [];
      sqlResult.changes = result.changes;
      sqlResult.lastInsertRowid = result.lastInsertRowid;
    }

    const endTime = performance.now();
    sqlResult.time = Math.round(endTime - startTime);

  } catch (error: any) {
    sqlResult.error = error.message || '执行出错';
    sqlResult.data = [];
    sqlResult.columns = [];
  } finally {
    executing.value = false;
  }
}

// 查看表结构
async function viewTableStructure(table: TableData) {
  structureDialog.tableName = table.name;
  try {
    const result = await window.elec.db.query(`PRAGMA table_info("${table.name}")`);
    structureDialog.columns = result || [];
    structureDialog.visible = true;
  } catch (error: any) {
    ElMessage.error(`获取表结构失败: ${error.message}`);
  }
}

// 查看表数据
function viewTableData(table: TableData) {
  dataDialog.tableName = table.name;
  dataDialog.page = 1;
  dataDialog.search = '';
  loadTableData();
  dataDialog.visible = true;
}

// 加载表数据
async function loadTableData() {
  dataDialog.loading = true;
  try {
    // 构建查询条件
    let whereClause = '';
    const params: any[] = [];

    if (dataDialog.search) {
      // 首先获取表的所有列
      const columnsInfo = await window.elec.db.query(`PRAGMA table_info("${dataDialog.tableName}")`);
      if (columnsInfo && columnsInfo.length > 0) {
        const searchClauses = columnsInfo.map((col: any) => {
          params.push(`%${dataDialog.search}%`);
          return `"${col.name}" LIKE ?`;
        });
        whereClause = `WHERE ${searchClauses.join(' OR ')}`;
      }
    }

    // 获取总记录数
    const countSql = `SELECT count(*) as total FROM "${dataDialog.tableName}" ${whereClause}`;
    const countResult = await window.elec.db.query(countSql, params);
    dataDialog.total = countResult[0].total;

    // 计算分页
    const offset = (dataDialog.page - 1) * dataDialog.pageSize;

    // 获取数据
    const sql = `SELECT * FROM "${dataDialog.tableName}" ${whereClause} LIMIT ${dataDialog.pageSize} OFFSET ${offset}`;
    const result = await window.elec.db.query(sql, params);

    if (result && result.length > 0) {
      dataDialog.columns = Object.keys(result[0]);
      dataDialog.data = result;
    } else {
      dataDialog.columns = [];
      dataDialog.data = [];
    }
  } catch (error: any) {
    ElMessage.error(`加载表数据失败: ${error.message}`);
  } finally {
    dataDialog.loading = false;
  }
}

// 处理数据搜索输入变化
function onDataSearchChange() {
  if (dataDialog.searchTimeout) {
    clearTimeout(dataDialog.searchTimeout);
  }

  dataDialog.searchTimeout = setTimeout(() => {
    dataDialog.page = 1;
    loadTableData();
  }, 500);
}

// 初始化
onMounted(async () => {
  await loadTables();
  await refreshDbInfo();
});
</script>

<style scoped>
.database-page {
  padding: 20px;
}

.info-card,
.tables-card,
.sql-card {
  margin-bottom: 20px;
}

.card-header {
  display: flex;
  justify-content: space-between;
  align-items: center;
}

.card-header h3 {
  margin: 0;
}

.sql-input {
  margin-bottom: 10px;
}

.result-header {
  display: flex;
  justify-content: space-between;
  align-items: center;
  margin: 10px 0;
}

.result-header h4 {
  margin: 0;
}

.result-summary {
  margin-top: 10px;
  text-align: right;
  color: #606266;
  font-size: 14px;
}

.data-dialog-toolbar {
  display: flex;
  justify-content: space-between;
  margin-bottom: 15px;
}

.dialog-pagination {
  margin-top: 15px;
  text-align: right;
}
</style>
